import type { Router } from "express";
import type { Ioracle, IcallbackFn } from "../../types";
import { decrypt } from "../../utils/privateKey";
import { BindParameters } from "oracledb";

const Query = (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  router.post("/getflow", (req, res) => {
    const { docid } = req.body;
    let sql = ` select a.docid,b.flow,b.phase_code,c.chinese_name action_klm,decode(b.approve,'Y','同意','N','退回','','簽核中')approve,b.comments
            ,to_char(b.last_update_time,'yyyy-mm-dd HH24:MI:SS')last_update_time
            from xxcc_new_engineercourse_header a,xxcc_new_engineercourse_flow b,user_info_n c
            where a.docid = '${docid}' and a.docid = b.docid and b.action_klm = c.account order by b.flow`;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/getdetail", (req, res) => {
    const { docid } = req.body;
    let sql = `select unit_code || '-' || b.description unit_code,objective,teacher_badge,teacher_dept,teacher_name,train_hours,trian_instructoin,
      to_char(start_date,'yyyy-mm-dd')start_date,to_char(end_date,'yyyy-mm-dd')end_date,assessment_method,assessment_result,
      assessment_score,to_char(actual_start_date,'yyyy-mm-dd')actual_start_date,to_char(actual_end_date,'yyyy-mm-dd')actual_end_date
    from xxcc_new_engineercourse_lines,course b where docid='${docid}' and unit_code = b.code`;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/getquerylist", (req, res) => {
    let { username, querytype, current, pagesize, queryParam } = req.body;
    queryParam = JSON.parse(queryParam);
    username = decrypt(username);
    let sql = "";
    try {
      if (querytype === "Count") {
        sql = ` select count(*)count from xxcc_new_engineercourse_header a where a.engineer_dept = '${queryParam.dept}'`;
        if (queryParam.org_code) {
          sql =
            sql +
            ` and exists(select 1 from department where code=a.engineer_dept and org_code='${queryParam.org_code}')`;
        }
        if (queryParam.badge) {
          sql = sql + ` and a.engineer_badge = '${queryParam.badge}'`;
        }
        if (queryParam.start_date) {
          sql =
            sql +
            ` and a.created_date >= to_date('${queryParam.start_date}','yyyy-mm-dd')`;
        }
        if (queryParam.start_date) {
          sql =
            sql +
            ` and a.created_date <= to_date('${queryParam.end_date}','yyyy-mm-dd')`;
        }
        callbackFn(res, oracle.connHrm, { sql });
      } else {
        sql = `select * from (select row_number() over(order by c.docid) as rowseq,c.* from(
                select a.docid,a.engineer_dept dept,a.engineer_badge badge,a.engineer_name name,decode(a.status,'Y','簽核中','N','已結案')status
                ,to_char(a.created_date,'yyyy-mm-dd')created_date,b.org_code
                from xxcc_new_engineercourse_header a,department b
                where a.engineer_dept = '${queryParam.dept}' and a.engineer_dept = b.code`;
        if (queryParam.org_code) {
          sql = sql + ` and b.org_code = '${queryParam.org_code}'`;
        }
        if (queryParam.badge) {
          sql = sql + ` and a.engineer_badge = '${queryParam.badge}'`;
        }
        if (queryParam.start_date) {
          sql =
            sql +
            ` and a.created_date >= to_date('${queryParam.start_date}','yyyy-mm-dd')`;
        }
        if (queryParam.start_date) {
          sql =
            sql +
            ` and a.created_date <= to_date('${queryParam.end_date}','yyyy-mm-dd')`;
        }
        sql =
          sql +
          `) c
                ) where rowseq between ${(current - 1) * pagesize + 1} and ${
            current * pagesize
          }`;
        callbackFn(res, oracle.connHrm, { sql });
      }
    } catch {
      res.send({
        code: 0,
        message: "數據庫異常，請聯繫系統管理員",
      });
    }
  });

  router.post("/queryauthuman", (req, res) => {
    const { badge } = req.body;
    let sql = ` select a.menu_code,c.chinese_name,b.label from xxcc_new_hrsystem_userpower a,xxcc_new_hrsystem_menu b,user_info_n c`;
    sql += ` where a.menu_code = b.menu_code and a.user_account = c.account and c.work_num ='${badge}'`;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/getgroup", (_req, res) => {
    let sql = ` select groupid,groupname from xxcc_new_hrsystem_group where groupname <> 'ADMIN'`;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/queryauthgroup", (req, res) => {
    const { groupid } = req.body;
    let sql = ` select a.groupid,b.label,c.groupname from xxcc_new_hrsystem_groupower a,xxcc_new_hrsystem_menu b,xxcc_new_hrsystem_group c
     where a.menu_code = b.menu_code and a.groupid = c.groupid and c.groupid =${groupid}`;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/querygrouphuman", (req, res) => {
    const { groupid } = req.body;
    let sql = ` select a.groupid,b.chinese_name,c.groupname from xxcc_new_hrsystem_groupmenber a,user_info_n b,xxcc_new_hrsystem_group c
     where a.groupid = ${groupid} and a.menber = b.account and a.groupid = c.groupid`;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/getmenu", (_req, res) => {
    let sql = ` select menu_code,label from xxcc_new_hrsystem_menu where menu_code <>  23`;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/addauth", (req, res) => {
    const { origin, target, type, username } = req.body;
    const originParse = JSON.parse(origin);
    const targetParse = JSON.parse(target);
    const usernameDec = decrypt(username);
    let sql = "";
    let bind: Array<BindParameters> = [];
    if (type === 0) {
      sql =
        " begin"
      sql += " declare v_count number;"
      sql += " begin"
      sql += " select count(*) into v_count from xxcc_new_hrsystem_userpower a,user_info_n b where menu_code = :menu_code and a.user_account = b.account and b.work_num = :work_num;"
      sql += " if v_count = 0 then"  
      sql += " insert into xxcc_new_hrsystem_userpower values(:menu_code,(select account from user_info_n where work_num = :work_num and organization_code in ('CC','CF','CH') and job_status = 'employed')";
      sql += " ,:username,sysdate);";
      sql += " end if;"
      sql += " end;"
      sql += " end;"
      for (let i = 0; i < originParse.length; i++) {
        for (let j = 0; j < targetParse.length; j++) {
          bind.push({
            menu_code: Number(targetParse[j].value),
            work_num: originParse[i].value,
            username: usernameDec,
          });
        }
      }
    } else if (type === 1) {
      sql =
        " begin"
      sql += " declare v_count number;"
      sql += " begin"
      sql += " select count(*) into v_count from xxcc_new_hrsystem_groupower where groupid=:groupid and menu_code = :menu_code;"
      sql += " if v_count =0 then"
      sql += " insert into xxcc_new_hrsystem_groupower values(:groupid,:username,sysdate,:menu_code);";
      sql += " end if;"
      sql += " end;"
      sql += " end;"
      for (let i = 0; i < originParse.length; i++) {
        for (let j = 0; j < targetParse.length; j++) {
          bind.push({
            groupid: Number(originParse[i].value),
            menu_code: Number(targetParse[j].value),
            username:usernameDec
          });
        }
      }
    } else if (type === 2) {
      sql =
      "begin"
      sql += " declare v_count number;"
      sql += " begin"
      sql += " select count(*) into v_count from xxcc_new_hrsystem_groupmenber a,user_info_n b where a.groupid = :groupid and a.menber = b.account and b.work_num = :work_num;"
      sql += " if v_count =0 then"
      sql += " insert into xxcc_new_hrsystem_groupmenber values(:groupid,(select account from user_info_n where work_num = :work_num and organization_code in ('CC','CF','CH') and job_status = 'employed'),:username,sysdate);";
      sql += " end if;"
      sql += " end;"
      sql += " end;"
      for (let i = 0; i < originParse.length; i++) {
        for (let j = 0; j < targetParse.length; j++) {
          bind.push({
            groupid: Number(originParse[i].value),
            work_num: targetParse[j].value,
            username: usernameDec,
          });
        }
      }
    }
    callbackFn(res, oracle.connHrm, { sql: sql, isMulti: true, params: bind });
  });

  return router;
};

export default Query;
